set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=100;
set hive.exec.max.dynamic.partitions.pernode=100;
insert overwrite table jms_dm.dm_project_customer_taking_send_sign_info_dt partition(dt)
select /*+ BROADCAST(tbl_customer) */
    tbl_customer.customer_uniname   --客户简称
     ,tbl_customer.customer_id        --客户id
     ,waybill.customer_code           --客户编码
     ,actual_taking_scansitecode as scansitecode   --网点编码
     ,network.name as network_name                 --网点名称
     ,network.subordinate_agent_code               --加盟商编码
     ,network.subordinate_agent_name               --加盟商名称
     ,network.first_franchisee_code                --代理商编码
     ,network.first_franchisee_name                --代理商名称
     ,waybill_trace.waybill_no  as  billcode       --单号
     ,waybill_trace.actual_taking_scantime as taking_time        --揽收时间
     ,waybill_trace.first_network_send_later_time as send_time   --发货时间
     ,if(waybill_trace.first_network_send_later_time is null,0,1) is_send    --是否发货
     ,waybill_trace.actual_sign_scantime as sign_time                        --签收时间
     ,if(waybill_trace.actual_sign_scantime is null,0,1) is_sign    --是否签收
     ,if(waybill_trace.actual_sign_scantime is null and tbl_difficult.billcode is not null,1,0) is_difficult --是否是问题件
     ,tbl_difficult.difficult_lable --问题建标签
     ,tbl_difficult.difficult_name  --问题件原因
     ,waybill_trace.is_refund       --是否退货
     ,waybill_trace.is_delete       --是否删除1未删除，2已删除
     ,date(waybill_trace.actual_taking_scantime) as dt
from
    (   --品牌客户
        select
        sys_customer.id as id  --自增id
            ,customer_config.customer_id as customer_id --string id
            ,min(customer_config.customer_uniname) as customer_uniname --简称
            ,sys_customer.code as customer_code --编码
            ,min(sys_customer.name) as customer_name --详细名称
            ,min(create_time) create_time
        from
        (
            select
            tb_id.mall_id
                ,project_custom_id as customer_id
                ,project_custom_name as customer_uniname
            from jms_dwd.dwd_tab_customer_monitor_deploy_dt
            lateral view explode(split(mill_id,',')) tb_id as mall_id
            where dt='{{ execution_date | cst_ds }}'
        ) customer_config
        left join
        (
            select code,name,id,create_time from jms_dim.dim_lmdm_sys_customer
        ) sys_customer
        on customer_config.mall_id=sys_customer.code
        group by customer_config.customer_id,sys_customer.code,sys_customer.id
    ) tbl_customer
    inner join
    ( --运单表
        select waybill_no,customer_code
        from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
        where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24 + 1) AND '{{ execution_date | cst_ds }}'
        and customer_code is not null
        and nvl(is_void, 0) = 0
    ) waybill
    on tbl_customer.customer_code=waybill.customer_code
    inner join
    (     --运单宽表
   -- select
   -- waybill_no
   -- ,actual_taking_scantime
   -- ,actual_taking_scansitecode
   -- ,first_network_send_later_time
   -- ,actual_sign_scantime
   -- ,input_time
   -- ,is_refund
   -- ,is_delete
   -- from jms_dwd.dwd_s03_whole_operations_waybill_trace_dt
   -- where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24 + 1) AND '{{ execution_date | cst_ds }}'
   -- and substr(actual_taking_scantime,1,10) BETWEEN date_add('{{ execution_date | cst_ds }}', -24 + 1) AND '{{ execution_date | cst_ds }}'
   -- and is_refund!='1'
   select
           waybill_no
           ,actual_taking_scantime
           ,actual_taking_scansitecode
           ,first_network_send_later_time
           ,actual_sign_scantime
           ,input_time
           ,is_refund
           ,is_delete
           from (
           select
           waybill_no
           --,nvl(taking_early_scantime,warehousing_early_scantime) actual_taking_scantime
           ,case when taking_early_scantime is  NULL then warehousing_early_scantime
                 when warehousing_early_scantime is null then taking_early_scantime
                 when taking_early_scantime is not null and warehousing_early_scantime is  not null  and taking_early_scantime <= warehousing_early_scantime then  taking_early_scantime
                 when taking_early_scantime is not null and warehousing_early_scantime is  not null  and taking_early_scantime > warehousing_early_scantime then  warehousing_early_scantime
            end as actual_taking_scantime
           --,nvl(taking_early_scansitecode,warehousing_early_scansitecode) actual_taking_scansitecode
           ,case when taking_early_scantime is  NULL then warehousing_early_scansitecode
                 when warehousing_early_scantime is null then taking_early_scansitecode
                 when taking_early_scantime is not null and warehousing_early_scantime is  not null  and taking_early_scantime <= warehousing_early_scantime then  taking_early_scansitecode
                 when taking_early_scantime is not null and warehousing_early_scantime is  not null  and taking_early_scantime > warehousing_early_scantime then  warehousing_early_scansitecode
            end as actual_taking_scansitecode
           ,first_network_send_later_time
           --,nvl(sign_early_scantime,outstore_early_scantime) actual_sign_scantime
           ,case when sign_early_scantime is  NULL then outstore_early_scantime
                 when outstore_early_scantime is null then sign_early_scantime
                 when sign_early_scantime is not null and outstore_early_scantime is  not null  and sign_early_scantime <= outstore_early_scantime then  sign_early_scantime
                 when sign_early_scantime is not null and outstore_early_scantime is  not null  and sign_early_scantime > outstore_early_scantime then  outstore_early_scantime
            end as actual_sign_scantime
           ,input_time
           ,is_refund
           ,is_delete
           from jms_dwd.dwd_wide_unsign_summary_waybill_dt
           where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -25 + 1) AND '{{ execution_date | cst_ds }}'
           and is_refund!='1'
           )t where substr(actual_taking_scantime,1,10) BETWEEN date_add('{{ execution_date | cst_ds }}', -24 + 1) AND '{{ execution_date | cst_ds }}'
   ) waybill_trace
   on waybill_trace.waybill_no=waybill.waybill_no
   left join  jms_dim.dim_lmdm_sys_network_expand network
   on waybill_trace.actual_taking_scansitecode=network.code
   left join
   (     ---问题件工单
       select
       billcode
       ,difficult_lable
       ,difficult_name
       from
       (
           select
            waybill_no as billcode
           ,problem_type_code as  difficult_lable --问题建标签
           ,problem_type  as  difficult_name  --问题件原因
           ,ROW_NUMBER() over(partition by waybill_no order by scan_time desc) rk
           from jms_dwd.dwd_tab_barscan_difficult_base_dt
           where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24 + 1) AND '{{ execution_date | cst_ds }}'
       ) t
       where rk=1
   )tbl_difficult
   on tbl_difficult.billcode=waybill_trace.waybill_no
distribute by pmod(hash(rand()), 3);